A powerful Node.js backend service that converts natural language queries into SQL statements using AI. Features intelligent vector search, dual database support, and flexible embedding options for production-ready SQL generation.
Clone and navigate to the backend directory:
cd backend/ai-sql-backend
Install dependencies:
npm install
Configure environment variables:
cp .env.example .env
# Edit .env with your configuration
Build the project:
npm run build
Start the server:
npm start
Create a .env file with the following variables:
# Embedding Method (local or azure)
EMBEDDING_METHOD=local
# Azure OpenAI Configuration (for production)
AZURE_OPENAI_KEY=your_azure_openai_key_here
AZURE_OPENAI_ENDPOINT=https://your-resource.openai.azure.com/
AZURE_OPENAI_DEPLOYMENT=gpt-4o-mini
AZURE_OPENAI_EMBEDDING=text-embedding-3-large
AZURE_OPENAI_API_VERSION=2024-08-01-preview
# Disable embeddings (fallback to keyword matching)
DISABLE_EMBEDDINGS=false
# Server Configuration
PORT=5050
NODE_ENV=development
# Schema File Paths
ENTITIES_SCHEMA_PATH=entities_prod_definition.txt
DMS_SCHEMA_PATH=dms_prod_definition.txt
EMBEDDING_METHOD=localEMBEDDING_METHOD=azure + Azure credentialsDISABLE_EMBEDDINGS=true# Set environment
export EMBEDDING_METHOD=local
# Start server
npm run dev
# Set environment
export EMBEDDING_METHOD=azure
export AZURE_OPENAI_KEY=your_key
export AZURE_OPENAI_ENDPOINT=https://your-resource.openai.azure.com/
# Start server
npm start
| Method | Endpoint | Description |
|---|---|---|
POST |
/api/sql |
Generate SQL from natural language |
POST |
/api/ai/answer |
Get AI analysis and database selection |
GET |
/api/vector/query |
Vector search for schema chunks |
GET |
/api/vector-health |
Health check for vector service |
GET |
/health |
Basic health check |
curl -X POST http://localhost:5050/api/sql \
-H "Content-Type: application/json" \
-d '{"question": "Top 10 organisations by revenue in 2024"}'
curl -X POST http://localhost:5050/api/ai/answer \
-H "Content-Type: application/json" \
-d '{"question": "Show me all people in the marketing department"}'
curl "http://localhost:5050/api/vector/query?text=revenue organizations"
curl http://localhost:5050/api/vector-health
{
"question": "Top 10 organisations by revenue in 2024",
"db_name": "entities",
"sql": "SELECT * FROM organisations ORDER BY revenue DESC LIMIT 10",
"params": [],
"notes": "Query targets the entities database and retrieves top organizations by revenue"
}
{
"db_name": "entities",
"answer": "This query should target the entities database to find organization revenue data",
"rationale": "The question asks about organizations and revenue, which are typically stored in the entities database",
"plan": {
"target": "entities",
"tables": ["organisations"],
"filters": ["revenue", "2024"]
}
}
{
"status": "healthy",
"vector_service": "integrated",
"method": "local",
"docs_loaded": 25,
"embedder_ready": true,
"embeddings_disabled": false
}
βββββββββββββββββββ ββββββββββββββββββββ βββββββββββββββββββ
β Frontend βββββΆβ Backend API βββββΆβ Vector Service β
β (Next.js) β β (Express) β β (Local/Azure) β
βββββββββββββββββββ ββββββββββββββββββββ βββββββββββββββββββ
β
βΌ
ββββββββββββββββββββ
β Azure OpenAI β
β (SQL Generation)β
ββββββββββββββββββββ
src/
βββ config/
β βββ env.ts # Environment configuration
βββ services/
β βββ vectorService.ts # Unified vector search service
β βββ answerService.ts # AI analysis service
β βββ sqlGenerator.ts # SQL generation service
β βββ schemaLoader.ts # Schema file loader
β βββ dbClassifier.ts # Database classifier
β βββ router.ts # API routes
βββ server.ts # Express server
βββ app.ts # Application entry point
npm run dev # Start development server with hot reload
npm run build # Build TypeScript to JavaScript
npm start # Start production server
npm run clean # Clean build directory
src/services/router.tssrc/services/ directorysrc/config/env.tstypes/ directory@xenova/transformers is installedEnable debug logging:
export DEBUG=*
npm run dev
vectorService.ts for optimal performancegit checkout -b feature-namegit commit -m 'Add feature'git push origin feature-nameThis project is licensed under the MIT License - see the LICENSE file for details.
For support and questions:
Made with β€οΈ for intelligent database querying